-- 出仓当日时效签收明细
with network_detail as (
    select code,
           name,
           agent_code,
           agent_name,
           fran_code,
           fran_name,
           virt_code,
           virt_name,
           manage_code as regional_id,
           manage_name as regional_desc
    from jms_dim.dim_network_whole_massage
    where is_delete=1
    and is_enable=1
    and network_type=6
    and agent_code is not null
    ),
   waybill_detail  as(
         select waybill_no,
                ordersource_code,
                --order_source_name,
                ordersource_name
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
       where dt>=date_sub('{{ execution_date  | cst_ds }}',45)
       and dt<='{{ execution_date  | cst_ds }}'
         ),
     active_sign_detail as ( select network_code,
       max(network_name) as network_name,
       count(1)          as active_deliver_sum,
       sum(is_09)        as active_deliver_09,
       sum(is_10)        as active_deliver_10,
       sum(is_11)        as active_deliver_11,
       sum(is_12)        as active_deliver_12,
       ordersource_code,
       max(ordersource_name) as ordersource_name,
       dt
from (
         select tmp.waybill_no,
                network_code,
                network_name,
                if(hour(scan_time) < 9, 1, 0)  as is_09,
                if(hour(scan_time) < 10, 1, 0) as is_10,
                if(hour(scan_time) < 11, 1, 0) as is_11,
                if(hour(scan_time) < 12, 1, 0) as is_12,
                scan_time,
                waybill_detail.ordersource_code,
                waybill_detail.ordersource_name,
                dt
         from (
                  select waybill_no,
                         scan_site_code as network_code,
                         scan_site as network_name,
                        scan_time,
                         row_number() over (partition by to_date(scan_time),waybill_no,scan_site_code order by scan_time )as rn,
                         to_date(scan_time)   as dt
                  from jms_dwd.dwd_tab_barscan_deliver_base_dt
              where dt>=date_sub('{{ execution_date  | cst_ds }}',20)
             and dt<='{{ execution_date  | cst_ds }}'
             and to_date(scan_time)>date_sub('{{ execution_date  | cst_ds }}',10)
             and to_date(scan_time)<='{{ execution_date  | cst_ds }}'
             and scan_site_code is not null
              ) tmp
    left join waybill_detail
    on tmp.waybill_no=waybill_detail.waybill_no
    where tmp.rn=1
     ) tmp2
group by network_code, dt,ordersource_code ),
     aging_sign_detail as (
         select network_code,
       max(network_name) as network_name,
       count(1)          as plan_delver_sum,
       sum(is_09)        as plan_delver_hour_09,
       sum(is_10)        as plan_delver_hour_10,
       sum(is_11)        as plan_delver_hour_11,
       sum(is_12)        as plan_delver_hour_12,
      order_source_code,
      max(order_source_name) as order_source_name ,
      --sum(if(is_09=1 and to_date(scan_time)=dt and  scan_time <=concat(dt,' 08:59:59') ,1,0)) as  plan_active_deliver_09,
       dt
from (
         select waybill_no,
                network_code,
                network_name,
                if(planned_arrival_time >='00:00:00' and planned_arrival_time <='07:30:00', 1, 0) as is_09,
                if(planned_arrival_time >='00:00:00' and planned_arrival_time <='08:30:00', 1, 0) as is_10,
                if(planned_arrival_time >='00:00:00' and planned_arrival_time <='09:30:00', 1, 0) as is_11,
                if(planned_arrival_time >='00:00:00' and planned_arrival_time <='10:30:00', 1, 0) as is_12,
                scan_time,
                order_source_code,
                order_source_name,
                dt
         from (
                  select billcode                as waybill_no,
                         final_sign_network_code as network_code,
                         final_sign_network_name as network_name,
                         deliver_time            as scan_time,
                         planned_arrival_time,
                         order_source_code,
                         order_source_name,
                         to_date(final_plan_sign_time)   as dt
                  from jms_dwd.dwd_terminal_sign_all_detail_new_dt
                  where dt between date_format(date_add('{{ execution_date  | cst_ds }}', -14), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date  | cst_ds }}', 0), 'yyyy-MM-dd')
                    and is_reject_reback_transfer = 2
                    and is_end_piece = 2
                    and end_center_send_time <= nvl(actual_sign_time, '9999-99-99')
                    and deliver_shift=1
                    and to_date(final_plan_sign_time) >= date_sub('{{ execution_date  | cst_ds }}', 9)
                    and to_date(final_plan_sign_time) <= '{{ execution_date  | cst_ds }}'

              ) tmp
     ) tmp2
group by network_code, dt,order_source_code
     ),
     plan_active_deliver_detail as (
         select network_code,
       max(network_name) as network_name,
      order_source_code,
      max(order_source_name) as order_source_name ,
      sum(if(is_09=1 and to_date(scan_time)=dt and  scan_time <=concat(dt,' 08:59:59') ,1,0)) as  plan_active_deliver_09,
      sum(if(is_10=1 and to_date(scan_time)=dt and  scan_time <=concat(dt,' 09:59:59') ,1,0)) as  plan_active_deliver_10,
      sum(if(is_11=1 and to_date(scan_time)=dt and  scan_time <=concat(dt,' 10:59:59') ,1,0)) as  plan_active_deliver_11,
      sum(if(is_12=1 and to_date(scan_time)=dt and  scan_time <=concat(dt,' 11:59:59') ,1,0)) as  plan_active_deliver_12,
       dt
from (
         select waybill_no,
                network_code,
                network_name,
                if(planned_arrival_time >='00:00:00' and planned_arrival_time <='07:30:00', 1, 0) as is_09,
                if(planned_arrival_time >='00:00:00' and planned_arrival_time <='08:30:00', 1, 0) as is_10,
                if(planned_arrival_time >='00:00:00' and planned_arrival_time <='09:30:00', 1, 0) as is_11,
                if(planned_arrival_time >='00:00:00' and planned_arrival_time <='10:30:00', 1, 0) as is_12,
                scan_time,
                order_source_code,
                order_source_name,
                dt
         from (
                  select billcode                as waybill_no,
                         nvl(deliver_network_code,final_sign_network_code) as network_code,
                         nvl(deliver_network_name,final_sign_network_name) as network_name,
                         deliver_time            as scan_time,
                         planned_arrival_time,
                         order_source_code,
                         order_source_name,
                         to_date(final_plan_sign_time)   as dt
                  from jms_dwd.dwd_terminal_sign_all_detail_new_dt
                  where dt between date_format(date_add('{{ execution_date  | cst_ds }}', -14), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date  | cst_ds }}', 0), 'yyyy-MM-dd')
                    and is_reject_reback_transfer = 2
                    and is_end_piece = 2
                    and end_center_send_time <= nvl(actual_sign_time, '9999-99-99')
                    and deliver_shift=1
                and to_date(final_plan_sign_time) >= date_sub('{{ execution_date  | cst_ds }}', 9)
                    and to_date(final_plan_sign_time) <= '{{ execution_date  | cst_ds }}'

              ) tmp
     ) tmp2
group by network_code, dt,order_source_code
     )
 insert overwrite table jms_dm.dm_day_deliver_aging_dt partition (dt)
--出仓当日时效签收监控
select
        virt_code  --虚拟代理区code
       ,virt_name  --虚拟代理区
       ,agent_code  --代理区code
       ,agent_name  --代理区
       ,fran_code   --加盟商code
       ,fran_name   --加盟商
       ,network_detail.code  --网点code
       ,name    --网点
       ,t1.active_deliver_sum  --实际出仓数量
       ,t1.plan_delver_sum      --计划出仓数量
       ,t1.active_deliver_09   --实际九点前出仓数量
       ,t1.active_deliver_10   --实际十点前出仓量
       ,t1.active_deliver_11   --实际11点前出仓量
       ,t1.active_deliver_12   --实际12点前出仓量
       ,t1.plan_delver_hour_09  --规划9点前出仓量
       ,t1.plan_delver_hour_10   --规划9点前出仓量
       ,t1.plan_delver_hour_11    --规划9点前出仓量
       ,t1.plan_delver_hour_12    --规划9点前出仓量
       ,t1.dt as date_time  -- 业务日期
       ,regional_id  --管理大区id
       ,regional_desc  --管理大区
       ,ordersource_code as order_source_code
       ,ordersource_name as order_source_name
       ,plan_active_deliver_09
       ,plan_active_deliver_10
       ,plan_active_deliver_11
       ,plan_active_deliver_12
       ,dt as dt  -- 分区日期
from (select

             network_code,
             max(network_name)as network_name,
             ordersource_code,
             max(ordersource_name)as ordersource_name,
             sum(active_deliver_sum) as active_deliver_sum,
             sum(active_deliver_09) as active_deliver_09,
             sum(active_deliver_10) as active_deliver_10,
             sum(active_deliver_11) as active_deliver_11,
             sum(active_deliver_12) as active_deliver_12,
             sum(plan_delver_hour_09) as plan_delver_hour_09,
             sum(plan_delver_hour_10) as plan_delver_hour_10,
             sum(plan_delver_hour_11) as plan_delver_hour_11,
             sum(plan_delver_hour_12) as plan_delver_hour_12,
             sum(plan_delver_sum) as plan_delver_sum,
             SUM(plan_active_deliver_09) as plan_active_deliver_09,
             SUM(plan_active_deliver_10) as plan_active_deliver_10,
             SUM(plan_active_deliver_11) as plan_active_deliver_11,
             SUM(plan_active_deliver_12) as plan_active_deliver_12,
             dt
      from (select network_code,
                   network_name,
                   active_deliver_sum,
                   active_deliver_09,
                   active_deliver_10,
                   active_deliver_11,
                   active_deliver_12,
                   ordersource_code,
                   ordersource_name,
                   0 as plan_delver_hour_09,
                   0 as plan_delver_hour_10,
                   0 as plan_delver_hour_11,
                   0 as plan_delver_hour_12,
                   0 as plan_delver_sum,
                   0 as plan_active_deliver_09,
                   0 as plan_active_deliver_10,
                   0 as plan_active_deliver_11,
                   0 as plan_active_deliver_12,
                   dt
            from active_sign_detail
            union all
            select network_code,
                   network_name,
                   0                 as active_deliver_sum,
                   0                 as active_deliver_09,
                   0                 as active_deliver_10,
                   0                 as active_deliver_11,
                   0                 as active_deliver_12,
                   order_source_code as ordersource_code,
                   order_source_name as ordersource_name,
                   plan_delver_hour_09,
                   plan_delver_hour_10,
                   plan_delver_hour_11,
                   plan_delver_hour_12,
                   plan_delver_sum,
                   0 as plan_active_deliver_09,
                   0 as plan_active_deliver_10,
                   0 as plan_active_deliver_11,
                   0 as plan_active_deliver_12,
                   dt
            from aging_sign_detail
                  union all
            select network_code,
                   network_name,
                   0                 as active_deliver_sum,
                   0                 as active_deliver_09,
                   0                 as active_deliver_10,
                   0                 as active_deliver_11,
                   0                 as active_deliver_12,
                   order_source_code as ordersource_code,
                   order_source_name as ordersource_name,
                   0 as plan_delver_hour_09,
                   0 as plan_delver_hour_10,
                   0 as plan_delver_hour_11,
                   0 as plan_delver_hour_12,
                   0 as plan_delver_sum,
                   plan_active_deliver_09,
                   plan_active_deliver_10,
                   plan_active_deliver_11,
                   plan_active_deliver_12,
                   dt
            from plan_active_deliver_detail
           ) tmp
      group by dt, network_code, ordersource_code
     )t1
join  network_detail
on  t1.network_code=network_detail.code
distribute by 5;